package cn.com.headfree.mouldshow.dao.impl;

import cn.com.headfree.entity.TTecManInnov;
import cn.com.headfree.mouldshow.dao.TTecManInnovDao;
import cn.com.headfree.vo.simple.TecManInnovVo;
import com.gta.edu.sdk.base.dao.impl.BaseDaoImpl;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository("/tTecManInnovDao")
public class TTecManInnovDaoImpl extends BaseDaoImpl<TTecManInnov> implements TTecManInnovDao {


    //成果名称,成果类别,内容摘要,记录形成时间,获奖时间,奖项授予单位,获奖级别,奖项等级,小组名称,获奖成员ERP编号,获奖成员姓名
    @Override
    public List<TecManInnovVo> findByBaseorgId(String deptId) throws Exception{
        Object[] params=new Object[1];
        StringBuilder sql=new StringBuilder("select t.id,t.achi_name,t.achi_type,t.award_name,t.summary,t.award_org,t.award_level,t.award_grade,t.group_name,t.award_date,u.`NAME` as user_name,t.create_time,p.erp_no from t_tec_man_innov t INNER JOIN t_part_rank p on t.id=p.item_id INNER JOIN t_dept_emp_rela d on p.erp_no=d.erp_no INNER JOIN (select * from t_org_dept o where o.path like ?) o2 on o2.id=d.dept_id LEFT JOIN isc_user u on p.erp_no=u.FAX WHERE t.is_valid='0' AND t.del_sign='0' ORDER BY t.create_time DESC");
//        StringBuilder sql=new StringBuilder("select t.id,t.achi_name,GET_DIC('AchiveType',t.achi_type) as achi_type,t.award_name,t.summary,t.award_org,GET_DIC('JygexinQcJb',t.award_level) as award_level,GET_DIC('JygexinQcDj',t.award_grade) as award_grade,t.group_name,t.award_date,u.`NAME` as user_name,t.create_time,p.erp_no from t_tec_man_innov t INNER JOIN t_part_rank p on t.id=p.item_id INNER JOIN t_dept_emp_rela d on p.erp_no=d.erp_no INNER JOIN (select * from t_org_dept o where o.path like ?) o2 on o2.id=d.dept_id LEFT JOIN isc_user u on p.erp_no=u.FAX WHERE t.is_valid='0' AND t.del_sign='0' ORDER BY t.create_time DESC");
//        StringBuilder sql=new StringBuilder("select t.id,t.achi_name,GET_DIC('AchiveType',t.achi_type) as achi_type,t.award_name,t.summary,t.award_org,GET_DIC('JygexinQcJb',t.award_level) as award_level,GET_DIC('JygexinQcDj',t.award_grade) as award_grade,t.group_name,t.award_date,DATE_FORMAT(t.award_date,'%Y-%m-%d %H:%i:%s') as award_date_str,u.`NAME` as user_name,t.create_time,DATE_FORMAT(t.create_time,'%Y-%m-%d %H:%i:%s') as create_time_str,p.erp_no from t_tec_man_innov t INNER JOIN t_part_rank p on t.id=p.item_id INNER JOIN t_dept_emp_rela d on p.erp_no=d.erp_no INNER JOIN (select * from t_org_dept o where o.path like ?) o2 on o2.id=d.dept_id LEFT JOIN isc_user u on p.erp_no=u.FAX WHERE t.is_valid='0' AND t.del_sign='0' ORDER BY t.create_time DESC");
//        StringBuilder sql=new StringBuilder("select t.id,t.achi_name,t.award_name,t.summary,t.award_org,GET_DIC('JygexinQcJb',t.award_level) as award_level,GET_DIC('JygexinQcDj',t.award_grade) as award_grade,t.group_name,t.award_date,DATE_FORMAT(t.award_date,'%Y-%m-%d %H:%i:%s') as award_date_str,u.xb_name as user_name,t.create_time,DATE_FORMAT(t.create_time,'%Y-%m-%d %H:%i:%s') as create_time_str,p.erp_no from t_tec_man_innov t INNER JOIN t_part_rank p on t.id=p.item_id INNER JOIN t_dept_emp_rela d on p.erp_no=d.erp_no INNER JOIN (select * from t_org_dept o where o.path like ?) o2 on o2.id=d.dept_id INNER JOIN t_isc_user_tmp u on p.erp_no=u.hr_erp WHERE t.is_valid='0' AND t.del_sign='0' ORDER BY t.create_time DESC\n");
        params[0] = "%"+deptId+"%";
        return this.findAllBySQL(TecManInnovVo.class, sql.toString(), null, false, params);
    }
}
